create table ml_hokej_klub(
  id integer not null,
  nazov varchar(30) not null,
  mesto varchar(15) not null,
  primary key (id)
);

create table ml_vzajomny_zapas(
  iddomaci integer not null,
  idhostia integer not null,
  datum date,
  goly_domaci integer,
  goly_hostia integer,
  primary key(iddomaci, idhostia)
);

ALTER TABLE ml_vzajomny_zapas ADD CONSTRAINT ml_const_domaci FOREIGN KEY (iddomaci) REFERENCES ml_hokej_klub;
ALTER TABLE ml_vzajomny_zapas ADD CONSTRAINT ml_const_hostia FOREIGN KEY (idhostia) REFERENCES ml_hokej_klub;

insert into ml_hokej_klub values
 (1, 'HC Kosice', 'Kosice'),
 (2, 'HC CounterStrike', 'Killing Vilage'),
 (3, 'HC Presov', 'Presov'),
 (4, 'HC Idioto', 'Padova'),
 (5, 'HC Lamers', 'Toronto');

insert into ml_vzajomny_zapas values
 (1,2,'2.2.2001',25,68),
 (1,4,'3.2.2002',0,15),
 (1,5,'5.12.2002',2,30),
 (2,3,'4.3.2001',2,5),
 (4,5,'6.2.2002',20,23);

select kosice.nazov, host.nazov,

CHAR(zapas.datum, EUR), 
(CASE WHEN zapas.goly_domaci < zapas.goly_hostia THEN 'prehra' 
      WHEN zapas.goly_domaci > zapas.goly_hostia THEN 'vyhra' 
      WHEN zapas.goly_domaci = zapas.goly_hostia THEN 'remiza'
      else 'nehralo sa'
END)

from ml_hokej_klub as kosice right outer JOIN ml_vzajomny_zapas as zapas ON kosice.id = zapas.iddomaci join ml_hokej_klub as host on host.id = zapas.idhostia

where kosice.id = 1



with kosicania(kto, skym, datum) as 
(
select kosice.nazov, host.nazov, zapas.datum
from ml_hokej_klub as kosice right outer JOIN ml_vzajomny_zapas as zapas ON kosice.id = zapas.iddomaci join ml_hokej_klub as host on host.id = zapas.idhostia
where kosice.id = 1
)

select * from kosicania;